SQL Aggregate Functions

Table of Contents

1. Basic Aggregate Functions

   • 1.1 COUNT – Total number of customers
   • 1.2 SUM – Total sales of all orders
   • 1.3 AVG – Average sales of all orders
   • 1.4 MAX – Highest customer score
   • 1.5 MIN – Lowest customer score

2. Grouped Aggregations – GROUP BY

   • 2.1 Per-customer order and sales summary

PDF Slides

1. Basic Aggregate Functions

1.1 COUNT – Total number of customers

Task: Write a query to find the total number of customers in the customers table.

SELECT COUNT(*) AS total_customers
FROM customers

1.2 SUM – Total sales of all orders

Task: Write a query to find the total sales amount across all orders in the orders table.

SELECT SUM(sales) AS total_sales
FROM orders

1.3 AVG – Average sales of all orders

Task: Write a query to calculate the average sales value of all rows in the orders table.

SELECT AVG(sales) AS avg_sales
FROM orders

1.4 MAX – Highest customer score

Task: Write a query to find the highest score among all customers in the customers table.

SELECT MAX(score) AS max_score
FROM customers

1.5 MIN – Lowest customer score

Task: Write a query to find the lowest score among all customers in the customers table.

SELECT MIN(score) AS min_score
FROM customers

2. Grouped Aggregations – GROUP BY

2.1 Per-customer order and sales summary

Task: For each customer, show:

• the number of orders
• the total sales
• the average sales
• the highest sales
• the lowest sales

Use the orders table and group the results by customer_id.

SELECT
    customer_id,
    COUNT(*) AS total_orders,
    SUM(sales) AS total_sales,
    AVG(sales) AS avg_sales,
    MAX(sales) AS highest_sales,
    MIN(sales) AS lowest_sales
FROM orders
GROUP BY customer_id